
**Import the dataset from the World Economic Forum years 2007-2017
import excel "${raw}/WEF-GCIHH.xlsx", firstrow clear


**Cleaning
keep if Attribute1 == "Value"
drop Attribute1 EconomyISO3 Attribute2 Attribute3 Partner IndicatorID
keep if strpos(Indicator, "pillar") > 0
keep if strpos(Indicator, "6th") > 0 | strpos(Indicator, "7th") > 0 | strpos(Indicator, "8th") > 0 | strpos(Indicator, "10th") > 0

rename I y2007
rename J y2008
rename K y2009
rename L y2010
rename M y2011
rename N y2012
rename O y2013
rename P y2014
rename Q y2015
rename R y2016
rename S y2017

* Reshape the data to long format
reshape long y, i(EconomyName Indicator) j(year)

* Calculate the mean for each pillar across all years
egen pillar_mean = mean(y), by(EconomyName Indicator)

* Calculate the mean over time for each country 
egen country_mean = mean(pillar_mean), by(EconomyName)

drop Indicator year y pillar_mean

rename EconomyName country
rename country_mean comp_idx
gen country_code =.
foreach pair in "8 Albania" "12 Algeria" "20 Andorra" "31 Azerbaijan" "32 Argentina" "36 Australia" "40 Austria" "50 Bangladesh" "51 Armenia" "68 Bolivia" "70 Bosnia and Herzegovina" "76 Brazil" "100 Bulgaria" "104 Myanmar" "112 Belarus" "124 Canada" "152 Chile" "156 China" "158 Taiwan, China" "170 Colombia" "191 Croatia" "196 Cyprus" "203 Czechia" "208 Denmark" "214 Dominican Republic" "218 Ecuador" "222 El Salvador" "231 Ethiopia" "233 Estonia" "246 Finland" "250 France" "268 Georgia" "276 Germany" "288 Ghana" "300 Greece" "320 Guatemala" "332 Haiti" "344 Hong Kong SAR, China" "348 Hungary" "352 Iceland" "356 India" "360 Indonesia" "364 Iran, Islamic Rep." "368 Iraq" "372 Ireland" "376 Israel" "380 Italy" "392 Japan" "398 Kazakhstan" "400 Jordan" "404 Kenya" "410 Korea, Rep." "414 Kuwait" "417 Kyrgyz Republic" "422 Lebanon" "428 Latvia" "434 Libya" "440 Lithuania" "442 Luxembourg" "446 Macao SAR, China" "458 Malaysia" "462 Maldives" "466 Mali" "470 Malta" "484 Mexico" "496 Mongolia" "498 Moldova" "499 Montenegro" "504 Morocco" "528 Netherlands" "554 New Zealand" "558 Nicaragua" "566 Nigeria" "578 Norway" "586 Pakistan" "604 Peru" "608 Philippines" "616 Poland" "620 Portugal" "630 Puerto Rico" "634 Qatar" "642 Romania" "643 Russian Federation" "646 Rwanda" "682 Saudi Arabia" "688 Serbia" "702 Singapore" "703 Slovak Republic" "704 Vietnam" "705 Slovenia" "710 South Africa" "716 Zimbabwe" "724 Spain" "752 Sweden" "756 Switzerland" "762 Tajikistan" "764 Thailand" "780 Trinidad and Tobago" "788 Tunisia" "792 Turkiye" "800 Uganda" "804 Ukraine" "807 North Macedonia" "818 Egypt, Arab Rep." "826 United Kingdom" "834 Tanzania" "840 United States" "854 Burkina Faso" "858 Uruguay" "860 Uzbekistan" "862 Venezuela, RB" "887 Yemen, Rep." "894 Zambia" "909 North Ireland" "915 Kosovo" {
    local code = word("`pair'", 1)
    local name = substr("`pair'", strpos("`pair'", " ") + 1, .)
    replace country_code = `code' if country == "`name'"
}

drop if country_code ==. 
duplicates drop country_code, force

save "${dta}/comp_idx.dta", replace
